package com.ldu.exam.dao;

import java.io.Serializable;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.ldu.exam.model.AccountModel;


public interface AccountRepository extends JpaRepository<AccountModel, Serializable> 
{
	AccountModel findByName( String name) ;
	
	AccountModel findByNameAndPassword( String name , String password) ;
	
	Page<AccountModel> findByNameContaining(String name, Pageable page);
	
	Page<AccountModel> findByNameNotIn( String[] names, Pageable page);
	
	AccountModel findByManagerClassId(String managerClassId);
	@Modifying
	@Query(nativeQuery=true,value="delete from rel_exam_account where account_id=?1")
	void removeRelExams( String id );
	
	@Modifying
	@Query(nativeQuery=true,value="delete from rel_role_account where account_id=?1")
	void removeRelRoles( String id ) ; 
	
	@Modifying
	@Query(nativeQuery=true,value="update t_class a set a.manager_id = '' where a.manager_Id=?1")
	void removeCla( String id) ;
	
	@Query(nativeQuery=true,value="select a.* from t_account a left join rel_role_account b on a.id = b.account_id where b.role_id =?1")
	Page<AccountModel> findStudents(@Param("id")String id, Pageable page);
	
	@Query(nativeQuery=true,value="select a.* from t_account a left join rel_role_account b on a.id = b.account_id where b.role_id =?1 and a.classid =?2")
	Page<AccountModel> findStudentsByClassId(String roleId,String classId, Pageable page);
	
	@Query(nativeQuery=true,value="select a.* from t_account a left join rel_role_account b on a.id = b.account_id where b.role_id =?1 and a.nickname =?2")
	Page<AccountModel> findStudentsByNickname(String roleId, String nickname ,Pageable page);
	
	@Query(nativeQuery=true,value="select * from t_account a where a.classId =?1")
	List<String> isClassIdExists(String classId);
}
